package database;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import S.po.StudentPO;
import S.po.TeacherPO;
import S.po.UserPO;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;


public class userConnector {

	final static String user = "root";
	final static String password = "000000";
	final static String url = "jdbc:mysql://127.0.0.1:3306/user";
	final static String userTable = "user";
	final static String studentTable = "studentinfo";
	final static String teacherTable = "teacherinfo";
	
	Connection con;
	Statement stmt;
//	final static String tableName = "user";
	
	public userConnector(){
		try {
			init();
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		
	public void init() throws ClassNotFoundException, SQLException{
		
		String driver = "com.mysql.jdbc.Driver";
	
		Class.forName(driver);
		con =  (Connection) DriverManager.getConnection(url, user, password);
		stmt = (Statement) con.createStatement();

	}

	public void queryPassword(String id){
		String sqlstr = "select * from "+userTable+" where id = '"+id+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			if(!rs.next()){
				System.out.println("no this user");
			}
			else{
				System.out.println(rs.getString("id")+"\t"+rs.getString("password")+"\t"+rs.getString("role"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public boolean changePassword(String id,String Password) {
		String sqlstr = "update "+userTable+" set password='"+Password+"' where id = '"+id+"'";
		int b = 0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b != 0;
	}
	
	public boolean addUser(String id,String role){
		String sqlstr = "insert "+userTable+"(id,password,role) " +
				"+ value('"+id+"',' +'"+id+"', '"+role+"'";
				//"insert staff(name, age, sex,address, depart, worklen,wage)"  
                //+ " value ('Tom1', 32, 'M', 'china','Personnel','3','3000')";
		int b = 0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b != 0;
	}
	
	public boolean addUser(String id,String name,String role){
		String sqlstr = "insert "+userTable+"(id,name,password,role) " +
				"+ value('"+id+"', '" +name+"', '"+id+"', '"+role+"'";
		int b = 0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b != 0;
	}
	
	public boolean deleteUser(UserPO up){
		String sqlstr1 = "delete from "+userTable+"where id = "+up.getId();
		String sqlstr2 = "delete from "+studentTable+"where id = "+up.getId();
		String sqlstr3 = "delete from "+teacherTable+"where id = "+up.getId();
		boolean b = true;
		try {
			b = stmt.executeUpdate(sqlstr1)!=0&&(stmt.executeUpdate(sqlstr2)!=0||stmt.executeUpdate(sqlstr3)!=0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}
	
	public boolean addStudentInfo(String id,String name){
		String sqlstr = "insert "+studentTable+"(id,name) " +
				"+ value('"+id+"',' +'"+name+"'";
		int b = 0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b != 0;
	}
	public boolean addTeacherInfo(String id,String name,String role){
		String sqlstr = "insert "+teacherTable+"(id,name,role) " +
				"+ value('"+id+"',' +'"+name+"','"+role+"'";
		int b = 0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b != 0;
	}
	
	public ArrayList<UserPO> queryAll(String role){
		ArrayList<UserPO> list = new ArrayList<UserPO>();
		String sqlstr = "select * from "+userTable+" where role = '"+role+"'";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(sqlstr);
		
			while(rs.next()){
				String id = rs.getString("id");
				String name = rs.getString("name");
				String password = rs.getString("password");
				StudentPO sp = new StudentPO(id,name,password,role);
				list.add(sp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public StudentPO queryStudentInfo(String id){
		StudentPO sp = null;
		String sqlstr = "select * from "+userTable+" where id = '"+id+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			if(!rs.next()){
				System.out.println("no this student");
			}
			else{
				
				ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
				sp = new StudentPO(rs.getString("id"),rs.getString("name"),
						rs.getString("password"),"student");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sp;
	}
	
	public TeacherPO queryTeacherInfo(String id){
		TeacherPO sp = null;
		String sqlstr = "select * from "+userTable+" where id = '"+id+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			if(!rs.next()){
				System.out.println("no this student");
			}
			else{
				
				ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
				sp = new TeacherPO(rs.getString("id"),rs.getString("name"),
						rs.getString("password"),rs.getString("role"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sp;	
			
		
	}
	
	
}
